﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Web.Mvc;

namespace ModalDownload.Models
{
    public class ClassTable
    {
        public int ID { get; set; }
        public string classname { get; set; }
        public string type { get; set; }
        public int parentid { get; set; }

        public static List<ClassTable> GetAllParentClass(string type)
        {
            List<ClassTable> list=new List<ClassTable>();
            DataTable dt = SQL_MS.myTable("select * from class where type='" + type + "' and parentid=0 order by id", CommandType.Text, null);
            foreach (DataRow dr in dt.Rows)
                list.Add(new ClassTable() { ID=int.Parse(dr["id"].ToString()),classname=(string)dr["classname"],type=(string)dr["type"],parentid=int.Parse(dr["parentid"].ToString()) });
            return list;
        }

        public static List<ClassTable> GetAllChildClass(string type)
        {
            List<ClassTable> list = new List<ClassTable>();
            DataTable dt = SQL_MS.myTable("select * from class where type='" + type + "' and parentid<>0 order by id", CommandType.Text, null);
            foreach (DataRow dr in dt.Rows)
                list.Add(new ClassTable() { ID = int.Parse(dr["id"].ToString()), classname = (string)dr["classname"], type = (string)dr["type"], parentid = int.Parse(dr["parentid"].ToString()) });
            return list;
        }

        public static List<ClassTable> GetAllClassByParent(int parentid)
        {
            List<ClassTable> list = new List<ClassTable>();
            DataTable dt = SQL_MS.myTable("select * from class where parentid='" + parentid + "' order by id", CommandType.Text, null);
            foreach (DataRow dr in dt.Rows)
                list.Add(new ClassTable() { ID = int.Parse(dr["id"].ToString()), classname = (string)dr["classname"], type = (string)dr["type"], parentid = int.Parse(dr["parentid"].ToString()) });
            return list;
        }

        public static ClassTable GetClassByID(string id)
        {
            ClassTable result=new ClassTable();
            DataTable dt=SQL_MS.myTable("select * from class where id='"+id+"'",CommandType.Text,null);
            if (dt.Rows.Count > 0)
            {
                DataRow dr=dt.Rows[0];
                result.ID = int.Parse(dr["id"].ToString());
                result.classname = (string)dr["classname"];
                result.type = (string)dr["type"];
                result.parentid = int.Parse(dr["parentid"].ToString());
            }
            return result;
        }

        public static List<SelectListItem> GetAllTypes()
        {
            List<SelectListItem> items = new List<SelectListItem>();
            items.Add(new SelectListItem() { Text = "3D模型", Value = "3d" });
            items.Add(new SelectListItem() { Text = "贴图素材", Value = "pic" });
            items.Add(new SelectListItem() { Text = "VR材质", Value = "vr" });
            return items;
        }
    }

    public class ModelTable
    {
        public int ID { get; set; }
        public int classid { get; set; }
        public string title { get; set;}
        public string descirption { get; set; }
        public string imagefilename { get; set; }
        public string download { get; set; }
        public DateTime createTime { get; set; }
        public bool recommend { get; set; }
        public DateTime recommendTime { get; set; }
        public bool hot { get; set; }
        public DateTime hotTime { get; set; }

        /// <summary>
        /// 获取推荐
        /// </summary>
        /// <param name="start"></param>
        /// <param name="length"></param>
        /// <returns></returns>
        public static List<ModelTable> GetRecommend(int start, int length,string type)
        {
            List<ModelTable> list = new List<ModelTable>();
            DataTable dt = SQL_MS.myTable(string.Format("select * from (select top {1} * from (select top ({0}+{1}) * from model where classid in (select id from class where type='{2}') and recommend=1 order by recommendTime desc) a order by recommendTime asc) b order by recommendTime desc",start,length,type),CommandType.Text,null);
            if(dt.Rows.Count==0)
                dt = SQL_MS.myTable(string.Format("select * from (select top {1} * from (select top ({0}+{1}) * from model where classid in (select id from class where type='{2}') order by recommendTime desc) a order by recommendTime asc) b order by recommendTime desc", start, length, type), CommandType.Text, null);
            foreach (DataRow dr in dt.Rows)
                list.Add(new ModelTable() { ID = int.Parse(dr["id"].ToString()), classid = int.Parse(dr["classid"].ToString()), title = (string)dr["title"], descirption = (string)dr["descirption"], imagefilename = (string)dr["imagefilename"], download = (string)dr["download"], createTime = DateTime.Parse(dr["createtime"].ToString()), recommend = (bool)dr["recommend"], recommendTime = DateTime.Parse(dr["recommendTime"].ToString()), hot = (bool)dr["hot"], hotTime = DateTime.Parse(dr["hotTime"].ToString()) });
            return list;
        }

        /// <summary>
        /// 获取热门
        /// </summary>
        /// <param name="start"></param>
        /// <param name="length"></param>
        /// <returns></returns>
        public static List<ModelTable> GetHot(int start, int length,string type)
        {
            List<ModelTable> list = new List<ModelTable>();
            DataTable dt = SQL_MS.myTable(string.Format("select * from (select top {1} * from (select top ({0}+{1}) * from model where classid in (select id from class where type='{2}') and hot=1 order by hotTime desc) a order by hotTime asc) b order by hotTime desc", start, length,type), CommandType.Text, null);
            if (dt.Rows.Count == 0)
                dt = SQL_MS.myTable(string.Format("select * from (select top {1} * from (select top ({0}+{1}) * from model where classid in (select id from class where type='{2}') order by hotTime desc) a order by hotTime asc) b order by hotTime desc", start, length,type), CommandType.Text, null);
            foreach (DataRow dr in dt.Rows)
                list.Add(new ModelTable() { ID = int.Parse(dr["id"].ToString()), classid = int.Parse(dr["classid"].ToString()), title = (string)dr["title"], descirption = (string)dr["descirption"], imagefilename = (string)dr["imagefilename"], download = (string)dr["download"], createTime = DateTime.Parse(dr["createtime"].ToString()), recommend = (bool)dr["recommend"], recommendTime = DateTime.Parse(dr["recommendTime"].ToString()), hot = (bool)dr["hot"], hotTime = DateTime.Parse(dr["hotTime"].ToString()) });
            return list;
        }

        public static List<ModelTable> GetModels(int page, int pagesize, string type, string parentid = "")
        {
            List<ModelTable> list = new List<ModelTable>();
            DataTable dt;
            if (string.IsNullOrEmpty(parentid))
                dt = SQL_MS.myTable(string.Format("select * from (select row_number() over(order by id desc) as rn,* from model where classid in (select id from class where [type]='{0}')) a where rn between (({1}-1)*{2}+1) and ({1}*{2})", type, page, pagesize), CommandType.Text, null);
            else
                dt = SQL_MS.myTable(string.Format("select * from (select row_number() over(order by id desc) as rn,* from model where classid in (select id from class where [type]='{0}' and (parentid='{3}' or id='{3}'))) a where rn between (({1}-1)*{2}+1) and ({1}*{2})", type, page, pagesize, parentid), CommandType.Text, null);
            foreach (DataRow dr in dt.Rows)
                list.Add(new ModelTable() { ID = int.Parse(dr["id"].ToString()), classid = int.Parse(dr["classid"].ToString()), title = (string)dr["title"], descirption = (string)dr["descirption"], imagefilename = (string)dr["imagefilename"], download = (string)dr["download"], createTime = DateTime.Parse(dr["createtime"].ToString()), recommend = (bool)dr["recommend"], recommendTime = DateTime.Parse(dr["recommendTime"].ToString()), hot = (bool)dr["hot"], hotTime = DateTime.Parse(dr["hotTime"].ToString()) });
            return list;
        }

        public static int GetTotal(string type,int classid=0)
        {
            DataTable dt;
            if (classid > 0)
                dt = SQL_MS.myTable(string.Format("select count(1) from model where classid in(select id from class where [type]='{0}' and (id ='{1}' or parentid='{1}'))", type,classid), CommandType.Text, null);
            else
                dt = SQL_MS.myTable(string.Format("select count(1) from model where classid in(select id from class where [type]='{0}')", type), CommandType.Text, null);
            return int.Parse(dt.Rows[0][0].ToString());
        }

        public bool Save(string type)
        {
            if (type == "add")
            {
                SqlParameter[] para = { new SqlParameter("@classid", this.classid), new SqlParameter("@title", this.title), new SqlParameter("@descirption", this.descirption), new SqlParameter("@imagefilename", this.imagefilename), new SqlParameter("@download", this.download), new SqlParameter("@recommend", this.recommend), new SqlParameter("@hot", this.hot) };
                int i = SQL_MS.ExecuteNonQuery("insert into model(classid,title,descirption,imagefilename,download,recommend,hot,recommendtime,hottime) values(@classid,@title,@descirption,@imagefilename,@download,@recommend,@hot,getDate(),getDate())", CommandType.Text, para);
                if (i > 0)
                    return true;
            }
            else if (type == "edit")
            {
                if (string.IsNullOrEmpty(this.imagefilename))
                {
                    SqlParameter[] para = { new SqlParameter("@ID", this.ID), new SqlParameter("@classid", this.classid), new SqlParameter("@title", this.title), new SqlParameter("@descirption", this.descirption), new SqlParameter("@download", this.download), new SqlParameter("@recommend", this.recommend), new SqlParameter("@hot", this.hot) };
                    int i = SQL_MS.ExecuteNonQuery("update model set classid=@classid,title=@title,descirption=@descirption,download=@download,recommend=@recommend,hot=@hot,recommendtime=getDate(),hottime=getDate() where ID=@ID", CommandType.Text, para);
                    if (i > 0)
                        return true;
                }
                else
                {
                    SqlParameter[] para = { new SqlParameter("@ID", this.ID), new SqlParameter("@classid", this.classid), new SqlParameter("@title", this.title), new SqlParameter("@descirption", this.descirption), new SqlParameter("@imagefilename", this.imagefilename), new SqlParameter("@download", this.download), new SqlParameter("@recommend", this.recommend), new SqlParameter("@hot", this.hot) };
                    int i = SQL_MS.ExecuteNonQuery("update model set classid=@classid,title=@title,descirption=@descirption,imagefilename=@imagefilename,download=@download,recommend=@recommend,hot=@hot,recommendtime=getDate(),hottime=getDate() where ID=@ID", CommandType.Text, para);
                    if (i > 0)
                        return true;
                }
            }
            return false;
        }

        public bool Delete()
        {
            int i = SQL_MS.ExecuteNonQuery("delete from model where id='"+this.ID+"'",CommandType.Text,null);
            if (i > 0)
                return true;
            return false;
        }

        public static ModelTable GetModel(string id)
        {
            DataTable dt=SQL_MS.myTable("select * from model where id='" + id + "'", CommandType.Text, null);
            if (dt.Rows.Count > 0)
            {
                DataRow dr = dt.Rows[0];
                return new ModelTable() { ID = int.Parse(dr["id"].ToString()), classid = int.Parse(dr["classid"].ToString()), title = (string)dr["title"], descirption = (string)dr["descirption"], imagefilename = (string)dr["imagefilename"], download = (string)dr["download"], createTime = DateTime.Parse(dr["createtime"].ToString()), recommend = (bool)dr["recommend"], recommendTime = DateTime.Parse(dr["recommendTime"].ToString()), hot = (bool)dr["hot"], hotTime = DateTime.Parse(dr["hotTime"].ToString()) };
            }
            return null;
        }

        public static List<ModelTable> GetModelsLikeTitle(string title)
        {
            List<ModelTable> list = new List<ModelTable>();
            DataTable dt = SQL_MS.myTable("select * from model where title like '%"+title+"%'", CommandType.Text, null);
            foreach (DataRow dr in dt.Rows)
                list.Add(new ModelTable() { ID = int.Parse(dr["id"].ToString()), classid = int.Parse(dr["classid"].ToString()), title = (string)dr["title"], descirption = (string)dr["descirption"], imagefilename = (string)dr["imagefilename"], download = (string)dr["download"], createTime = DateTime.Parse(dr["createtime"].ToString()), recommend = (bool)dr["recommend"], recommendTime = DateTime.Parse(dr["recommendTime"].ToString()), hot = (bool)dr["hot"], hotTime = DateTime.Parse(dr["hotTime"].ToString()) });
            return list;
        }
    }

    public class ADTable
    {
        public int ID { get; set; }
        public string title { get; set; }
        public int width { get; set; }
        public int height { get; set; }
        public string code { get; set; }
        public bool valid { get; set; }

        public static List<ADTable> GetModels(int page, int pagesize)
        {
            List<ADTable> list=new List<ADTable>();
            DataTable dt = SQL_MS.myTable(string.Format("select * from (select row_number() over(order by id) as rn,* from ad) a where rn between (({0}-1)*{1}+1) and ({0}*{1})", page, pagesize), CommandType.Text, null);
            foreach (DataRow dr in dt.Rows)
                list.Add(new ADTable() { ID = int.Parse(dr["id"].ToString()), title = dr["title"].ToString(), width = int.Parse(dr["width"].ToString()), height = int.Parse(dr["height"].ToString()), code = dr["code"].ToString(), valid = (bool)dr["valid"] });
            return list;
        }

        public static int GetTotal()
        {
            DataTable dt = SQL_MS.myTable("select count(1) from ad", CommandType.Text, null);
            return int.Parse(dt.Rows[0][0].ToString());
        }

        public static ADTable GetModel(string id)
        {
            DataTable dt = SQL_MS.myTable("select * from ad where id='" + id + "'", CommandType.Text, null);
            if (dt.Rows.Count > 0)
            {
                DataRow dr = dt.Rows[0];
                return new ADTable() { ID = int.Parse(dr["id"].ToString()), title = dr["title"].ToString(), width = int.Parse(dr["width"].ToString()), height = int.Parse(dr["height"].ToString()), code = dr["code"].ToString(), valid = (bool)dr["valid"] };
            }
            return null;
        }

        public static HtmlString GetADCode(string id)
        {
            ADTable model = GetModel(id);
            if (model == null||!model.valid)
            {
                return new HtmlString("");
            }
            else if(string.IsNullOrEmpty(model.code))
                return new HtmlString("<div style=\"width:"+model.width+"px;height:"+model.height+"px;text-align: center;line-height:"+model.height+"px;vertical-align: middle;background-color:white;font-size:large;color:black;\">" + (string.IsNullOrEmpty(model.title) ? "广告位招租" : (model.title+"招租")) + "</div>"); 
            return new HtmlString(model.code);
        }

        public bool Save(string viewmode)
        {
            if (this.code == null)
                this.code = "";
            if (viewmode == "edit")
            {
                SqlParameter[] para = { new SqlParameter("@ID", this.ID), new SqlParameter("@code", this.code) };
                int i = SQL_MS.ExecuteNonQuery("update ad set code=@code where ID=@ID", CommandType.Text, para);
                if (i > 0)
                    return true;
            }
            return false;
        }
    }

    public class SQL_MS
    {
        private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
        public static void CacheParameters(string cacheKey, params SqlParameter[] cmdParms)
        {
            parmCache[cacheKey] = cmdParms;
        }
        public static SqlParameter[] GetCachedParameters(string cacheKey)
        {
            SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey];

            if (cachedParms == null)
                return null;
            SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length];
            for (int i = 0, j = cachedParms.Length; i < j; i++)
                clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone();
            return clonedParms;
        }
        public static readonly string strConn = System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
        public static int ExecuteNonQuery(string cmdText, CommandType cmdType, params SqlParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                PrepareCommand(conn, cmd, cmdText, cmdType, cmdParms, null);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                Close_conn(conn);
                return val;
            }
        }
        public static DataTable myTable(string cmdText, CommandType cmdType, params SqlParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                PrepareCommand(conn, cmd, cmdText, cmdType, cmdParms, null);
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                sda.Fill(ds);

                Close_conn(conn);
                return ds.Tables[0];


            }
        }
        public static void PrepareCommand(SqlConnection conn, SqlCommand cmd, string cmdText, CommandType cmdType, SqlParameter[] cmdParms, SqlTransaction trans)
        {

            if (conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;
            if (trans != null)
                cmd.Transaction = trans;
            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }

        public static bool ExecuteStoreProcedure(string[] cmdText, CommandType cmdType, params SqlParameter[] cmdParms)
        {
            bool success = true;
            int i = 0;
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                conn.Open();
                SqlTransaction trans = conn.BeginTransaction(); /*开始事务*/
                try
                {

                    PrepareCommand(conn, cmd, cmdText[0], cmdType, null, trans);
                    cmd.Parameters.Add(cmdParms[0]);
                    cmd.Parameters.Add(cmdParms[1]);
                    cmd.Parameters.Add(cmdParms[2]);
                    cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();

                    PrepareCommand(conn, cmd, cmdText[1], cmdType, null, trans);
                    cmd.Parameters.Add(cmdParms[3]);
                    cmd.Parameters.Add(cmdParms[4]);

                    cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();


                    trans.Commit();
                }
                catch
                {
                    success = false;
                    trans.Rollback();
                }
                finally
                {
                    conn.Close();
                }
            }
            return success;
        }

        public static void Close_conn(SqlConnection conn)
        {
            if (conn.State == ConnectionState.Open)
                conn.Close();



        }


    }
}